﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace YJ.Data.MSSQL
{
    public class HomeItems : YJ.Data.Interface.IHomeItems
    {
        private DBHelper dbHelper = new DBHelper();
        /// <summary>
        /// 构造函数
        /// </summary>
        public HomeItems()
        {
        }
        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="model">YJ.Data.Model.HomeItems实体类</param>
        /// <returns>操作所影响的行数</returns>
        public int Add(YJ.Data.Model.HomeItems model)
        {
            string sql = @"INSERT INTO HomeItems
				(ID,Type,Name,Title,DataSourceType,DataSource,Ico,BgColor,Color,DBConnID,LinkURL,UseOrganizes,UseUsers,Sort,Note) 
				VALUES(@ID,@Type,@Name,@Title,@DataSourceType,@DataSource,@Ico,@BgColor,@Color,@DBConnID,@LinkURL,@UseOrganizes,@UseUsers,@Sort,@Note)";
            SqlParameter[] parameters = new SqlParameter[]{
				new SqlParameter("@ID", SqlDbType.UniqueIdentifier, -1){ Value = model.ID },
				new SqlParameter("@Type", SqlDbType.Int, -1){ Value = model.Type },
				new SqlParameter("@Name", SqlDbType.NVarChar, 1000){ Value = model.Name },
				new SqlParameter("@Title", SqlDbType.NVarChar, 1000){ Value = model.Title },
				new SqlParameter("@DataSourceType", SqlDbType.Int, -1){ Value = model.DataSourceType },
				model.DataSource == null ? new SqlParameter("@DataSource", SqlDbType.VarChar, -1) { Value = DBNull.Value } : new SqlParameter("@DataSource", SqlDbType.VarChar, -1) { Value = model.DataSource },
				model.Ico == null ? new SqlParameter("@Ico", SqlDbType.VarChar, 2000) { Value = DBNull.Value } : new SqlParameter("@Ico", SqlDbType.VarChar, 2000) { Value = model.Ico },
				model.BgColor == null ? new SqlParameter("@BgColor", SqlDbType.VarChar, 50) { Value = DBNull.Value } : new SqlParameter("@BgColor", SqlDbType.VarChar, 50) { Value = model.BgColor },
				model.Color == null ? new SqlParameter("@Color", SqlDbType.VarChar, 50) { Value = DBNull.Value } : new SqlParameter("@Color", SqlDbType.VarChar, 50) { Value = model.Color },
				model.DBConnID == null ? new SqlParameter("@DBConnID", SqlDbType.UniqueIdentifier, -1) { Value = DBNull.Value } : new SqlParameter("@DBConnID", SqlDbType.UniqueIdentifier, -1) { Value = model.DBConnID },
				model.LinkURL == null ? new SqlParameter("@LinkURL", SqlDbType.VarChar, 2000) { Value = DBNull.Value } : new SqlParameter("@LinkURL", SqlDbType.VarChar, 2000) { Value = model.LinkURL },
				model.UseOrganizes == null ? new SqlParameter("@UseOrganizes", SqlDbType.VarChar, -1) { Value = DBNull.Value } : new SqlParameter("@UseOrganizes", SqlDbType.VarChar, -1) { Value = model.UseOrganizes },
				model.UseUsers == null ? new SqlParameter("@UseUsers", SqlDbType.VarChar, -1) { Value = DBNull.Value } : new SqlParameter("@UseUsers", SqlDbType.VarChar, -1) { Value = model.UseUsers },
				model.Sort == null ? new SqlParameter("@Sort", SqlDbType.Int, -1) { Value = DBNull.Value } : new SqlParameter("@Sort", SqlDbType.Int, -1) { Value = model.Sort },
				model.Note == null ? new SqlParameter("@Note", SqlDbType.NVarChar, 4000) { Value = DBNull.Value } : new SqlParameter("@Note", SqlDbType.NVarChar, 4000) { Value = model.Note }
			};
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="model">YJ.Data.Model.HomeItems实体类</param>
        public int Update(YJ.Data.Model.HomeItems model)
        {
            string sql = @"UPDATE HomeItems SET 
				Type=@Type,Name=@Name,Title=@Title,DataSourceType=@DataSourceType,DataSource=@DataSource,Ico=@Ico,BgColor=@BgColor,Color=@Color,DBConnID=@DBConnID,LinkURL=@LinkURL,UseOrganizes=@UseOrganizes,UseUsers=@UseUsers,Sort=@Sort,Note=@Note
				WHERE ID=@ID";
            SqlParameter[] parameters = new SqlParameter[]{
				new SqlParameter("@Type", SqlDbType.Int, -1){ Value = model.Type },
				new SqlParameter("@Name", SqlDbType.NVarChar, 1000){ Value = model.Name },
				new SqlParameter("@Title", SqlDbType.NVarChar, 1000){ Value = model.Title },
				new SqlParameter("@DataSourceType", SqlDbType.Int, -1){ Value = model.DataSourceType },
				model.DataSource == null ? new SqlParameter("@DataSource", SqlDbType.VarChar, -1) { Value = DBNull.Value } : new SqlParameter("@DataSource", SqlDbType.VarChar, -1) { Value = model.DataSource },
				model.Ico == null ? new SqlParameter("@Ico", SqlDbType.VarChar, 2000) { Value = DBNull.Value } : new SqlParameter("@Ico", SqlDbType.VarChar, 2000) { Value = model.Ico },
				model.BgColor == null ? new SqlParameter("@BgColor", SqlDbType.VarChar, 50) { Value = DBNull.Value } : new SqlParameter("@BgColor", SqlDbType.VarChar, 50) { Value = model.BgColor },
				model.Color == null ? new SqlParameter("@Color", SqlDbType.VarChar, 50) { Value = DBNull.Value } : new SqlParameter("@Color", SqlDbType.VarChar, 50) { Value = model.Color },
				model.DBConnID == null ? new SqlParameter("@DBConnID", SqlDbType.UniqueIdentifier, -1) { Value = DBNull.Value } : new SqlParameter("@DBConnID", SqlDbType.UniqueIdentifier, -1) { Value = model.DBConnID },
				model.LinkURL == null ? new SqlParameter("@LinkURL", SqlDbType.VarChar, 2000) { Value = DBNull.Value } : new SqlParameter("@LinkURL", SqlDbType.VarChar, 2000) { Value = model.LinkURL },
				model.UseOrganizes == null ? new SqlParameter("@UseOrganizes", SqlDbType.VarChar, -1) { Value = DBNull.Value } : new SqlParameter("@UseOrganizes", SqlDbType.VarChar, -1) { Value = model.UseOrganizes },
				model.UseUsers == null ? new SqlParameter("@UseUsers", SqlDbType.VarChar, -1) { Value = DBNull.Value } : new SqlParameter("@UseUsers", SqlDbType.VarChar, -1) { Value = model.UseUsers },
				model.Sort == null ? new SqlParameter("@Sort", SqlDbType.Int, -1) { Value = DBNull.Value } : new SqlParameter("@Sort", SqlDbType.Int, -1) { Value = model.Sort },
				model.Note == null ? new SqlParameter("@Note", SqlDbType.NVarChar, 4000) { Value = DBNull.Value } : new SqlParameter("@Note", SqlDbType.NVarChar, 4000) { Value = model.Note },
				new SqlParameter("@ID", SqlDbType.UniqueIdentifier, -1){ Value = model.ID }
			};
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 删除记录
        /// </summary>
        public int Delete(Guid id)
        {
            string sql = "DELETE FROM HomeItems WHERE ID=@ID";
            SqlParameter[] parameters = new SqlParameter[]{
				new SqlParameter("@ID", SqlDbType.UniqueIdentifier){ Value = id }
			};
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 将DataRedar转换为List
        /// </summary>
        private List<YJ.Data.Model.HomeItems> DataReaderToList(SqlDataReader dataReader)
        {
            List<YJ.Data.Model.HomeItems> List = new List<YJ.Data.Model.HomeItems>();
            YJ.Data.Model.HomeItems model = null;
            while (dataReader.Read())
            {
                model = new YJ.Data.Model.HomeItems();
                model.ID = dataReader.GetGuid(0);
                model.Type = dataReader.GetInt32(1);
                model.Name = dataReader.GetString(2);
                model.Title = dataReader.GetString(3);
                model.DataSourceType = dataReader.GetInt32(4);
                if (!dataReader.IsDBNull(5))
                    model.DataSource = dataReader.GetString(5);
                if (!dataReader.IsDBNull(6))
                    model.Ico = dataReader.GetString(6);
                if (!dataReader.IsDBNull(7))
                    model.BgColor = dataReader.GetString(7);
                if (!dataReader.IsDBNull(8))
                    model.Color = dataReader.GetString(8);
                if (!dataReader.IsDBNull(9))
                    model.DBConnID = dataReader.GetGuid(9);
                if (!dataReader.IsDBNull(10))
                    model.LinkURL = dataReader.GetString(10);
                if (!dataReader.IsDBNull(11))
                    model.UseOrganizes = dataReader.GetString(11);
                if (!dataReader.IsDBNull(12))
                    model.UseUsers = dataReader.GetString(12);
                if (!dataReader.IsDBNull(13))
                    model.Sort = dataReader.GetInt32(13);
                if (!dataReader.IsDBNull(14))
                    model.Note = dataReader.GetString(14);
                List.Add(model);
            }
            return List;
        }
        /// <summary>
        /// 查询所有记录
        /// </summary>
        public List<YJ.Data.Model.HomeItems> GetAll()
        {
            string sql = "SELECT * FROM HomeItems";
            SqlDataReader dataReader = dbHelper.GetDataReader(sql);
            List<YJ.Data.Model.HomeItems> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List;
        }
        /// <summary>
        /// 查询记录数
        /// </summary>
        public long GetCount()
        {
            string sql = "SELECT COUNT(*) FROM HomeItems";
            long count;
            return long.TryParse(dbHelper.GetFieldValue(sql), out count) ? count : 0;
        }
        /// <summary>
        /// 根据主键查询一条记录
        /// </summary>
        public YJ.Data.Model.HomeItems Get(Guid id)
        {
            string sql = "SELECT * FROM HomeItems WHERE ID=@ID";
            SqlParameter[] parameters = new SqlParameter[]{
				new SqlParameter("@ID", SqlDbType.UniqueIdentifier){ Value = id }
			};
            SqlDataReader dataReader = dbHelper.GetDataReader(sql, parameters);
            List<YJ.Data.Model.HomeItems> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List.Count > 0 ? List[0] : null;
        }

        /// <summary>
        /// 得到分页数据
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="query"></param>
        /// <param name="name"></param>
        /// <param name="title"></param>
        /// <returns></returns>
        public List<YJ.Data.Model.HomeItems> GetList(out string pager, string query = "", string name = "", string title="", string type = "")
        {
            List<SqlParameter> parList = new List<SqlParameter>();
            StringBuilder sql = new StringBuilder("SELECT *,ROW_NUMBER() OVER(ORDER BY Type,Sort) AS PagerAutoRowNumber FROM HomeItems WHERE 1=1 ");

            if (!name.IsNullOrEmpty())
            {
                sql.Append(" AND CHARINDEX(@Name,Name)>0");
                parList.Add(new SqlParameter("@Name", name));
            }

            if (!title.IsNullOrEmpty())
            {
                sql.Append(" AND CHARINDEX(@Title,Title)>0");
                parList.Add(new SqlParameter("@Title", title));
            }

            if (!type.IsNullOrEmpty())
            {
                sql.Append(" AND Type=@Type");
                parList.Add(new SqlParameter("@Type", type));
            }
           
            long count;
            int size = YJ.Utility.Tools.GetPageSize();
            int number = YJ.Utility.Tools.GetPageNumber();
            string sql1 = dbHelper.GetPaerSql(sql.ToString(), size, number, out count, parList.ToArray());
            pager = YJ.Utility.Tools.GetPagerHtml(count, size, number, query);
            SqlDataReader dataReader = dbHelper.GetDataReader(sql1, parList.ToArray());
            List<YJ.Data.Model.HomeItems> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List;
        }

        /// <summary>
        /// 得到分页数据
        /// </summary>
        /// <param name="count"></param>
        /// <param name="query"></param>
        /// <param name="name"></param>
        /// <param name="title"></param>
        /// <param name="type"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<YJ.Data.Model.HomeItems> GetList(out long count, int size, int number, string name = "", string title = "", string type = "", string order = "")
        {
            List<SqlParameter> parList = new List<SqlParameter>();
            StringBuilder sql = new StringBuilder("SELECT *,ROW_NUMBER() OVER(ORDER BY " + (order.IsNullOrEmpty() ? "Type,Sort" : order) + ") AS PagerAutoRowNumber FROM HomeItems WHERE 1=1 ");

            if (!name.IsNullOrEmpty())
            {
                sql.Append(" AND CHARINDEX(@Name,Name)>0");
                parList.Add(new SqlParameter("@Name", name));
            }

            if (!title.IsNullOrEmpty())
            {
                sql.Append(" AND CHARINDEX(@Title,Title)>0");
                parList.Add(new SqlParameter("@Title", title));
            }

            if (!type.IsNullOrEmpty())
            {
                sql.Append(" AND Type=@Type");
                parList.Add(new SqlParameter("@Type", type));
            }

            string sql1 = dbHelper.GetPaerSql(sql.ToString(), size, number, out count, parList.ToArray());
            SqlDataReader dataReader = dbHelper.GetDataReader(sql1, parList.ToArray());
            List<YJ.Data.Model.HomeItems> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List;
        }

        /// <summary>
        /// 得到最大排序号
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public int GetMaxSort(int type)
        {
            string sql = "SELECT MAX(Sort) FROM HomeItems WHERE Type=" + type;
            string sort = dbHelper.GetFieldValue(sql);
            return sort.ToInt(0) + 5;
        }
    }
}